package com.adam.management.excel;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     *
     * @Desc 读取Excel中的sheet，方法暂时只支出xls格式的Excel
     * @param filePath 要读取的文件的路径
     * @param sheetIndex 要去读的Excel中的sheet的索引
     * @return List<List<String>> sheetList ->读取的内容
     * @author lj
     * @Date 2018年12月12日 上午10:40:13
     */
    public static List<List<Object>> readExcel(InputStream inputStream,int sheetIndex) throws Exception{

        //实例化一个list对象，保存sheet里面的内容
        List<List<Object>> sheetList = new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        int sheetQuantity = workbook.getNumberOfSheets();
        if (sheetIndex>sheetQuantity){
            System.out.println("读取的sheet数量不足!请检查!");
            throw new Exception("读取的sheet数量不足!请检查!");
        }
        // 按sheet的下标获取要读取的sheet
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        //if(sheet==null){continue;}

        //包含头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部
        for (int i = sheet.getFirstRowNum(); i <=sheet.getLastRowNum(); i++) {

            //创建内层list对象
            ArrayList<Object> listRow = new ArrayList<>();

            // 获取第sheet的row对象
            HSSFRow row = sheet.getRow(i);

            //去掉空行
            if (row == null) {continue;}

            //如果实际存在的cell数量不等于lastcellNum则跳过
            if (row.getLastCellNum()!=row.getPhysicalNumberOfCells()){continue;}

            for (int j = row.getFirstCellNum(); j <row.getLastCellNum(); j++) {
                int cellLastNum = row.getLastCellNum();
                int cellTotalNum = row.getPhysicalNumberOfCells();
                //对cell进行list组装
                Cell cell = row.getCell(j);
                listRow.add(getCellValue(cell));
            }

            //将行list添加至外层list
            sheetList.add(listRow);
        }

        workbook.close();//关闭资源
        return sheetList;
    }

    /**
     * 读取cell单元格中的值
     * @param cell 单元格　
    ∗ @return
     * @throws
     * @author Administrator　　
    ∗ @date 2019/5/30 17:05
     */
    public static Object getCellValue(Cell cell){

        Object value = null;

        //String cellStyle = cell.getCellStyle().getDataFormatString();

        switch (cell.getCellTypeEnum()){
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case FORMULA:
                value = formatCellValue(cell);
                break;
            case NUMERIC:
                value = formatCellValue(cell);
                break;
            case BLANK:
                value = "";
                break;
            case BOOLEAN:
                value = cell.getStringCellValue();
                break;
            case ERROR:
                //value = new DecimalFormat("0.00").format(0.00);
                value = "非法字符";
                break;
            case _NONE:
                value = "";
                break;
            default:
                break;
        }

        return value;
    }


    /**
     * 格式化cell中的值
     * @param cell row的单元格　
    ∗ @return
     * @throws
     * @author Administrator　　
    ∗ @date 2019/5/31 11:11
     */
    public static Object formatCellValue(Cell cell){

        Object value;

        //格式化字符类型的数字
        DecimalFormat df = new DecimalFormat("0");
        //日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
        //格式化数字
        DecimalFormat df2 = new DecimalFormat("0.00");

        if("General".equals(cell.getCellStyle().getDataFormatString())){
            value = df.format(cell.getNumericCellValue());
        }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
            value = sdf.format(cell.getDateCellValue());
        }else{
            value = df2.format(cell.getNumericCellValue());
        }

        return value;
    }


    /**
     * 导出excel
     * @param excel_name 导出的excel路径（需要带.xlsx)
     * @param headList  excel的标题备注名称
     * @param fieldList excel的标题字段（与数据中map中键值对应）
     * @param dataList  excel数据
     * @throws Exception
     */
    public static XSSFWorkbook createExcel(String excel_name, String[] headList,
                                           String[] fieldList, List<Map<String, String>> dataList,
                                           int[] columnWidth)
            throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();

        //循环设置列宽
        for(int i=0; i<columnWidth.length; i++) {
            sheet.setColumnWidth(i,columnWidth[i]);
        }
        // 在索引0的位置创建行（最顶端的行）
        XSSFRow row = sheet.createRow(0);

        //设置excel头（第一行）的头名称
        for (int i = 0; i < headList.length; i++) {

            // 在索引0的位置创建单元格（左上端）
            XSSFCell cell = row.createCell(i);

            // 在单元格中输入一些内容
            cell.setCellValue(headList[i]);
        }

        //添加数据
        for (int n = 0; n < dataList.size(); n++) {

            // 在索引1的位置创建行（最顶端的行）
            XSSFRow row_value = sheet.createRow(n + 1);
            Map<String, String> dataMap = dataList.get(n);
            // 设置一个变量以创建row之cell
            int key = 0;

            for(String value : fieldList) {
                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = row_value.createCell(key);
                // 在单元格中输入一些内容
                cell.setCellValue(String.valueOf(dataMap.get(value)));
                key++;
            }
        }

        return workbook;
    }


    /**
     * 把读出的object类型的表格数据转化为可以插入的String 类型
     * @param list
    ∗ @return
     * @throws
     * @author Administrator　　
    ∗ @date 2019/5/31 14:14
     */
    public static List<List<String>> transferObjectToString(List<List<Object>> list) {

        List<List<String>> stringList = new ArrayList<>();
        for (List<Object> exlist:list){
            List<String> internalList = new ArrayList<>();
            for (Object inlist:exlist){
                internalList.add((String) inlist);
            }
            stringList.add(internalList);
        }

        return stringList;
    }



    public static void doExportByBrowser(HttpServletResponse response, XSSFWorkbook workbook,

                                         String fileName){

        try{

            response.reset();
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
            OutputStream out = response.getOutputStream();

            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(out);
            bufferedOutPut.flush();
            workbook.write(bufferedOutPut);
            out.close();
            bufferedOutPut.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}